/* 
 * File:   DBAccess.cpp
 * Author: Wang
 * 
 * Created on April 10, 2013, 3:51 PM
 */

#include "DBAccess.h"
#include <mysql.h>
#include<stdio.h>
#include<iostream>
#include<string>
#include<vector>
#include<iomanip>
using namespace std;

/**
 * Constructor, instantiate sql and connect to database
 */
DBAccess::DBAccess() {
    connect = mysql_init(NULL);
    if (connect == NULL) {
        cout << "MySQL Initialization failed" << endl;
    }
    connect = mysql_real_connect(connect, SERVER, USER, PASS, DATABASE, 0, NULL, 0);
    if (connect != NULL) {
        cout << "database connection successful\n";
    } else cout << "dtatbase connection failed\n";

    /*
    //for debug use
    vector<string> p;
    p.push_back("user");
    //p.push_back("1");
    //p.push_back("VIP ticket");
    query_Database("view",p);*/


}

DBAccess::DBAccess(const DBAccess& orig) {
    this->connect = orig.connect;
}

DBAccess::~DBAccess() {
}

/**
 * SQL for register process
 * @param parameter
 * @return 
 */
string DBAccess::register_Database(const vector<string> &parameter) {
    string username = parameter[0];
    string password = parameter[1];
    string st = "INSERT INTO user VALUE ('" + username + "'" + ",'" + password + "')";
    const char *statement = st.c_str();
    int result = mysql_query(connect, statement);
    if (result == 0) {
        cout << "register successful!\n";
        return "register successful!\n";
    } else {
        cout << "register failed due to mysql_query() fail!\n";
        return "register failed due to mysql_query() fail!\n";
    }
}

/**
 * SQL for login process
 * @param parameter
 * @return 
 */
string DBAccess::login_Database(const vector<string> &parameter) {
    string username = parameter[0];
    string password = parameter[1];
    string st = "SELECT * FROM user WHERE username='" + username + "' " + "AND password='" + password + "'";
    const char *statement = st.c_str();
    int result = mysql_query(connect, statement);
    if (result == 0) {
        MYSQL_RES *res_set;
        res_set = mysql_store_result(connect);
        int numrows = mysql_num_rows(res_set);
        if (numrows == 1) {
            cout << "user verified!\n" << "welcome to the Basketball Ticket Booking System, " + username + "!\n";
            return "succeed";
        } else {
            cout << "verify failed, username or password wrong!\n";
            return "failed";
        }
    } else {
        cout << "login failed due to mysql_query() fail!\n";
        return "login failed due to mysql_query() fail!\n";
    }
}

/**
 * SQL for list process
 * @param parameter
 * @return 
 */
string DBAccess::list_Database(const vector<string> &parameter) {
    string team = parameter[0];
    string date = parameter[1];

    cout << team << " ";
    cout << date << endl;

    string token = "-";
    int result = -1;
    if (date == token) {
        cout << "111111111111" << endl;
        if (team.empty()) {//date and team are all empty, then show matches in 7 days
            string st = "SELECT * FROM game WHERE date >= CURDATE() AND date <= CURDATE() + INTERVAL 7 DAY";
            const char *statement = st.c_str();
            result = mysql_query(connect, statement);
        } else {//date is empty but team is selected, then show that team match in 7 days
            string st = "SELECT * FROM game WHERE (hometeam='" + team + "' " + "OR visitteam =" + "'" + team + "' " +
                    ") AND date >= CURDATE() AND date <= CURDATE() + INTERVAL 3 DAY";
            const char *statement = st.c_str();
            result = mysql_query(connect, statement);
        }
    } else if (team == token) {//team is empty but date is selected, show matches that day
        cout << "2222222222222" << endl;
        string st = "SELECT * FROM game WHERE date ='" + date + "'" + "AND date <= CURDATE() + INTERVAL 7 DAY AND date >= CURDATE()";
        const char *statement = st.c_str();
        result = mysql_query(connect, statement);
    } else {//team and matches all selected, show that match
        string st = "SELECT * FROM game WHERE (hometeam='" + team + "' " + "OR visitteam =" + "'" + team + "' " +
                ") AND date ='" + date + "'";
        const char *statement = st.c_str();
        result = mysql_query(connect, statement);
    }
    if (result == 0) {
        MYSQL_RES *res_set;
        res_set = mysql_store_result(connect);
        MYSQL_ROW row;
        int numrows = mysql_num_rows(res_set);
        if (numrows == 0) {
            cout << "there is no match coorespond to your search!\n";
            return "there is no match coorespond to your search!\n";
        } else {
            string r;
            //cout<<left<<setw(8)<<"gameID"<<setw(12)<<"date"<<setw(15)<<"hometeam"<<setw(15)<<"visitteam"<<setw(15)<<"VIP tickets"<<setw(18)<<"normal tickets"<<"economic tickets\n";
            string r1 = "gameID";
            r1.resize(8, ' ');
            string r2 = "date";
            r2.resize(12, ' ');
            string r3 = "hometeam";
            r3.resize(15, ' ');
            string r4 = "visitteam";
            r4.resize(15, ' ');
            string r5 = "VIP tickets";
            r5.resize(15, ' ');
            string r6 = "normal tickets";
            r6.resize(18, ' ');
            string r7 = "economic tickets";
            r7.resize(18, ' ');
            string r8 = "location\n";
            r = r1 + r2 + r3 + r4 + r5 + r6 + r7 + r8;
            while ((row = mysql_fetch_row(res_set)) != NULL) {
                //cout<<left<<setw(8)<<row[0]<<setw(12)<<row[1]<<setw(15)<<row[2]<<setw(15)<<row[3]<<setw(15)<<row[4]<<setw(18)<<row[5]<<row[6]<<"\n";
                string l1(row[0]);
                l1.resize(8, ' ');
                string l2(row[1]);
                l2.resize(12, ' ');
                string l3(row[2]);
                l3.resize(15, ' ');
                string l4(row[3]);
                l4.resize(15, ' ');
                string l5(row[7]);
                l5.resize(15, ' ');
                string l6(row[8]);
                l6.resize(18, ' ');
                string l7 = (row[9]);
                l7.resize(18, ' ');
                string l8 = (row[10]);
                r += l1 + l2 + l3 + l4 + l5 + l6 + l7 + l8 + "\n";
            }
            cout << r;
            return r;
        }
    } else {
        cout << "list fail due to mysql_query() failed!\n";
        return "list fail due to mysql_query() failed!";
    }
}

/**
 * SQL for book process
 * @param parameter
 * @return 
 */
string DBAccess::book_Database(const vector<string> &parameter) {
    string username = parameter[0];
    string gameID = parameter[1];
    string tickettype = parameter[2];
    string tty;
    int result = -1;
    if (tickettype == "VIP") {
        string st = "UPDATE game SET hticket = hticket -1 WHERE gameID='" + gameID + "'";
        const char *statement = st.c_str();
        result = mysql_query(connect, statement);
    } else if (tickettype == "normal") {
        string st = "UPDATE game SET mticket = mticket -1 WHERE gameID='" + gameID + "'";\
		const char *statement = st.c_str();
        result = mysql_query(connect, statement);
    } else if (tickettype == "economic ticket") {
        string st = "UPDATE game SET lticket = lticket -1 WHERE gameID='" + gameID + "'";
        const char *statement = st.c_str();
        result = mysql_query(connect, statement);
    } else {
        cout << "Booking ticket type is not correct!\n";
        return "Booking ticket type is not correct!\n";
    }
    if (result == 0) {
        string st = "INSERT INTO tickets VALUES ('" + username + "', '" + gameID + "', '" + tickettype + "')";
        const char *statement = st.c_str();
        result = mysql_query(connect, statement);
        if (result == 0) {
            cout << "user booking information updated\n" << "Booking tickets successful!\n";
            return "succeed";
        } else {
            cout << "update user booking information failed due to  mysql_query() failed!\n";
            return "update user booking information failed due to  mysql_query() failed!\n";
        }
    } else {
        cout << "Ticket sold out!\n";
        return "Ticket sold out!\n";
    }
}

/**
 * SQL for view process
 * @param parameter
 * @return 
 */
string DBAccess::view_Database(const vector<string> &parameter) {
    string username = parameter[0];
    string st = "SELECT * FROM tickets WHERE username='" + username + "'";
    const char *statement = st.c_str();
    int result = mysql_query(connect, statement);
    if (result == 0) {
        MYSQL_RES *res_set;
        res_set = mysql_store_result(connect);
        MYSQL_ROW row;
        int numrows = mysql_num_rows(res_set);
        if (numrows == 0) {
            cout << "there is no match you have booked!\n";
            return "there is no match you have booked!\n";
        } else {
            //cout<<"your booking information:\n";
            //cout<<left<<setw(10)<<"gameID"<<setw(12)<<"date"<<setw(15)<<"hometeam"<<setw(15)<<"visitteam"<<setw(12)<<"tickettype\n";
            string rp;
            string r1 = "gameID";
            r1.resize(8, ' ');
            string r2 = "date";
            r2.resize(12, ' ');
            string r3 = "hometeam";
            r3.resize(15, ' ');
            string r4 = "visitteam";
            r4.resize(15, ' ');
            string r5 = "ticket type";
            r5.resize(18, ' ');
            string r6 = "price";
            r6.resize(10, ' ');
            string r7 = "location\n";
            rp = "your booking information:\n" + r1 + r2 + r3 + r4 + r5 + r6 + r7;
            rp += "-----------------------------------------------------------------------------------------------\n";
            while ((row = mysql_fetch_row(res_set)) != NULL) {
                string tp(row[1]);
                string s = "SELECT * FROM game WHERE gameID='" + tp + "'";
                const char *es = s.c_str();
                int res = mysql_query(connect, es);
                if (res == 0) {
                    MYSQL_RES *rs;
                    rs = mysql_store_result(connect);
                    int nr = mysql_num_rows(rs);
                    if (nr == 1) {
                        MYSQL_ROW r = mysql_fetch_row(rs);
                        //cout<<left<<setw(10)<<r[0]<<setw(12)<<r[1]<<setw(15)<<r[2]<<setw(15)<<r[3]<<setw(12)<<row[2]<<endl;
                        string l1(r[0]);
                        l1.resize(8, ' ');
                        string l2(r[1]);
                        l2.resize(12, ' ');
                        string l3(r[2]);
                        l3.resize(15, ' ');
                        string l4(r[3]);
                        l4.resize(15, ' ');
                        string l5(row[2]);
                        string l6;
                        if (l5 == "VIP") {
                            string t(r[7]);
                            l6 = t;
                            l6.resize(10, ' ');
                        } else if (l5 == "normal") {
                            string t(r[8]);
                            l6 = t;
                            l6.resize(10, ' ');
                        } else if (l5 == "economic") {
                            string t(r[9]);
                            l6 = t;
                            l6.resize(10, ' ');
                        } else {
                            l6 = "no price";
                            l6.resize(10, ' ');
                        }
                        l5.resize(18, ' ');
                        string l7(r[10]);
                        rp += l1 + l2 + l3 + l4 + l5 + l6 + l7 + "\n";
                    } else {
                        cout << "lookup error in game table, more than one game with same ID!\n";
                        rp += "lookup error in game table, more than one game with same ID!\n";
                    }
                } else {
                    cout << "lookup errorin game table due to mysql_query() failed!\n";
                    rp += "lookup errorin game table due to mysql_query() failed!\n";
                }
            }
            cout << rp;
            return rp;
        }
    } else {
        cout << "lookup error in tickets table due to mysql_query() failed!\n";
        return "lookup error in tickets table due to mysql_query() failed!\n";
    }
}

/**
 * Facade pattern, forward request to corresponding function
 * @param cmd
 * @param parameter
 * @return 
 */
string DBAccess::query_Database(string cmd, const vector<string> &parameter) {
    if (cmd == "register") {
        return register_Database(parameter);
    } else if (cmd == "login") {
        return login_Database(parameter);
    } else if (cmd == "list") {
        return list_Database(parameter);
    } else if (cmd == "book") {
        return book_Database(parameter);
    } else if (cmd == "view") {
        return view_Database(parameter);
    } else {
        return "command wrong";
    }
}




